package com.hugesoft.utils;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Calendar;
import java.util.List;
import java.util.Locale;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import android.os.Environment;
import android.text.format.DateFormat;

import com.hugesoft.logic.managers.LogicManager;
import com.hugesoft.logic.managers.common.Constans;
import com.hugesoft.logic.module.Bus;
import com.hugesoft.logic.module.DriverLicence;
import com.hugesoft.logic.module.Police;
import com.hugesoft.logic.module.Traffic;

public class ExcelUtil {

	private static String[] busTitleArray = { "采集时间", "车辆号牌", "驾驶人", "驾驶证号码",
			"起点", "终点", "驾驶资格", "驾龄", "核载人数", "实载人数", "年检", "安全设施", "轮胎磨损",
			"培训", "随车电话", "备注", "检查民警", "签名","照片1", "照片2", "照片3", "照片4" };

	private static String[] trafficTitleArray = { "采集时间", "车辆号牌", "驾驶人",
			"驾驶证号码", "起点", "终点", "驾驶资格", "驾龄", "核载质量", "实载质量", "年检", "安全设施",
			"轮胎磨损", "随车电话", "准驾品名", "施救方式", "检查民警", "签名","照片1", "照片2", "照片3", "照片4" };
	
	public static String createWorkBook() throws IOException {
		HSSFWorkbook wb = new HSSFWorkbook();

		createBusSheet(wb);
		createTrafficSheet(wb);

		// 创建一个文件 命名为workbook.xls
		String path = Environment.getExternalStorageDirectory()
				.getAbsolutePath()
				+ "/hugesoft/jxgs/Record"
				+ DateFormat.format("yyyyMMdd_kkmmss",
						Calendar.getInstance(Locale.CHINA)).toString() + ".xls";
		FileOutputStream fileOut = new FileOutputStream(path);
		// 把上面创建的工作簿输出到文件中
		wb.write(fileOut);
		// 关闭输出流
		fileOut.close();
		return path;
	}

	private static void createBusSheet(HSSFWorkbook wb) throws IOException {
		List<Bus> businfolist = LogicManager.busManager().loadallbus();
		HSSFSheet sheet_ky = wb.createSheet("客运车辆");

		HSSFCellStyle titlestyle = wb.createCellStyle();
		titlestyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);// 设置填充的背景颜色
		titlestyle.setFillForegroundColor(HSSFColor.GREEN.index);
		titlestyle.setAlignment(CellStyle.ALIGN_CENTER);

		HSSFFont titlefont = wb.createFont();
		titlefont.setFontName(HSSFFont.FONT_ARIAL);// 字体
		titlefont.setFontHeightInPoints((short) 18);// 字号
		titlefont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
		titlefont.setColor(HSSFColor.GREEN.index);// 颜色
		titlestyle.setFont(titlefont);

		HSSFCellStyle cellStyle = wb.createCellStyle();
		cellStyle.setAlignment(CellStyle.ALIGN_CENTER);

		HSSFFont cellFont = wb.createFont();
		cellFont.setFontName(HSSFFont.FONT_ARIAL);
		cellFont.setFontHeightInPoints((short) 25);//

		cellStyle.setFont(cellFont);
		// Row 行
		// Cell 方格
		// Row 和 Cell 都是从0开始计数的

		// 创建一行，在页sheet上
		Row rowtitle = sheet_ky.createRow((short) 0);
		// Or do it on one line.
		for (int k = 0; k < busTitleArray.length; k++) {
			sheet_ky.setColumnWidth(k, 5120);
			if (k == 0) {
				sheet_ky.setColumnWidth(k, 25 * 512);
			}
			else if (k == 3) {
				sheet_ky.setColumnWidth(k, 25 * 512);
			}
			else if (k == 14) {
				sheet_ky.setColumnWidth(k, 15 * 512);
			} else if (k >= 17) {
				sheet_ky.setColumnWidth(k, 15 * 512);
			}
			Cell cell = rowtitle.createCell(k);
			cell.setCellValue(busTitleArray[k]);
			cell.setCellStyle(titlestyle);
		}

		for (int i = 0; i < businfolist.size(); i++) {
			Bus businfo = businfolist.get(i);
			DriverLicence driverinfo = LogicManager.driverLicenceManager()
					.loadlicenceinfo(businfo.getDriverid());
			Police policeinfo = LogicManager.policeManager().loadpolice(
					businfo.getPoliceid());
			Row row = sheet_ky.createRow(i + 1);
			row.setHeight((short) (0.5 * 5120));
			row.createCell(0).setCellValue(
					DateFormat.format("yyyy-MM-dd kk:mm:ss",
							businfo.getCheckdate()).toString());
							
			row.createCell(1).setCellValue(businfo.getPlatenumber());
			row.createCell(2).setCellValue(driverinfo.getDrivername());
			row.createCell(3).setCellValue(driverinfo.getLicenceno());
			row.createCell(4).setCellValue(businfo.getStartposition());
			row.createCell(5).setCellValue(businfo.getEndposition());
			row.createCell(6).setCellValue(driverinfo.getType());
			row.createCell(7).setCellValue(driverinfo.getDriverage());
			row.createCell(8).setCellValue(businfo.getPermittedpassengernum());
			row.createCell(9).setCellValue(businfo.getActualpassengernum());
			row.createCell(10).setCellValue(
					businfo.getIsyearchecked() ? "已年检" : "未年检");
			row.createCell(11).setCellValue(
					businfo.getIssafedeviceready() ? "齐全" : "不齐全");
			row.createCell(12).setCellValue(
					businfo.getIswheelbroken() ? "严重" : "正常");
			row.createCell(13).setCellValue(businfo.getIstaught() ? "已培训" : "未培训");
			row.createCell(14).setCellValue(businfo.getTelnum());
			row.createCell(15).setCellValue(businfo.getComment());
			row.createCell(16).setCellValue(policeinfo.getName());
			row.createCell(17);
			String sigpath = businfo.getSig();
			if (!sigpath.equalsIgnoreCase("null") && sigpath.length() != 0) {
				HSSFPatriarch patriarch = sheet_ky.createDrawingPatriarch();
				insertImage(wb, patriarch,
						BitmapUtil.loadBitmapToByteArray(sigpath), i + 1, 17,
						1);
			}

			
			String imgurls[] = businfo.getPic().split(Constans.PICPATHSPLITOR);
			for (int j = 0; j < imgurls.length; j++) {
				String path = imgurls[j];
				if (path.equalsIgnoreCase("null") || path.length() == 0) {
					continue;
				}
				HSSFPatriarch patriarch = sheet_ky.createDrawingPatriarch();
				insertImage(wb, patriarch,
						BitmapUtil.loadBitmapToByteArray(path), i + 1, 18 + j,
						1);
			}

			for (int k = 0; k < 18; k++) {
				Cell cell = row.getCell(k);
				cell.setCellStyle(cellStyle);
			}
		}
	}


	private static void createTrafficSheet(HSSFWorkbook wb) throws IOException {
		List<Traffic> trafficinfolist = LogicManager.trafficManager()
				.loadalltraffic();
		HSSFSheet sheet_whp = wb.createSheet("危化品车辆");
		HSSFCellStyle titlestyle = wb.createCellStyle();
		titlestyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);// 设置填充的背景颜色
		titlestyle.setFillForegroundColor(HSSFColor.GREEN.index);
		titlestyle.setAlignment(CellStyle.ALIGN_CENTER);

		HSSFFont titlefont = wb.createFont();
		titlefont.setFontName(HSSFFont.FONT_ARIAL);// 字体
		titlefont.setFontHeightInPoints((short) 18);// 字号
		titlefont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
		titlefont.setColor(HSSFColor.GREEN.index);// 颜色
		titlestyle.setFont(titlefont);

		HSSFCellStyle cellStyle = wb.createCellStyle();
		cellStyle.setAlignment(CellStyle.ALIGN_CENTER);

		HSSFFont cellFont = wb.createFont();
		cellFont.setFontName(HSSFFont.FONT_ARIAL);
		cellFont.setFontHeightInPoints((short) 25);//

		cellStyle.setFont(cellFont);
		// Row 行
		// Cell 方格
		// Row 和 Cell 都是从0开始计数的

		// 创建一行，在页sheet上
		Row rowtitle = sheet_whp.createRow((short) 0);
		// Or do it on one line.
		for (int k = 0; k < trafficTitleArray.length; k++) {
			sheet_whp.setColumnWidth(k, 5120);
			if (k == 0) {
				sheet_whp.setColumnWidth(k, 25 * 512);
			}
			else if (k == 3) {
				sheet_whp.setColumnWidth(k, 25 * 512);
			}
			else if (k == 13) {
				sheet_whp.setColumnWidth(k, 15 * 512);
			} else if (k >= 18) {
				sheet_whp.setColumnWidth(k, 15 * 512);
			}
			Cell cell = rowtitle.createCell(k);
			cell.setCellValue(trafficTitleArray[k]);
			cell.setCellStyle(titlestyle);
		}

		for (int i = 0; i < trafficinfolist.size(); i++) {
			Traffic trafficinfo = trafficinfolist.get(i);
			DriverLicence driverinfo = LogicManager.driverLicenceManager()
					.loadlicenceinfo(trafficinfo.getDriverid());
			Police policeinfo = LogicManager.policeManager().loadpolice(
					trafficinfo.getPoliceid());
			Row row = sheet_whp.createRow(i + 1);
			row.setHeight((short) (0.5 * 5120));
			row.createCell(0).setCellValue(
					DateFormat.format("yyyy-MM-dd kk:mm:ss",
							trafficinfo.getCheckdate()).toString());
			row.createCell(1).setCellValue(trafficinfo.getPlatenumber());
			row.createCell(2).setCellValue(driverinfo.getDrivername());
			row.createCell(3).setCellValue(driverinfo.getLicenceno());
			row.createCell(4).setCellValue(trafficinfo.getStartposition());
			row.createCell(5).setCellValue(trafficinfo.getEndposition());
			row.createCell(6).setCellValue(driverinfo.getType());
			row.createCell(7).setCellValue(driverinfo.getDriverage());
			row.createCell(8).setCellValue(trafficinfo.getPermittedquality());
			row.createCell(9).setCellValue(trafficinfo.getActualquality());
			row.createCell(10).setCellValue(
					trafficinfo.getIsyearchecked() ? "已年检" : "未年检");
			row.createCell(11).setCellValue(
					trafficinfo.getIssafedeviceready() ? "齐全" : "不齐全");
			row.createCell(12).setCellValue(
					trafficinfo.getIswheelbroken() ? "严重" : "正常");
			row.createCell(13).setCellValue(trafficinfo.getTelnum());
			row.createCell(14).setCellValue(trafficinfo.getPermittedgoods());
			row.createCell(15).setCellValue(trafficinfo.getSavemethod());
			row.createCell(16).setCellValue(policeinfo.getName());
			row.createCell(17);
			String sigpath = trafficinfo.getSig();
			if (!sigpath.equalsIgnoreCase("null") && sigpath.length() != 0) {
				HSSFPatriarch patriarch = sheet_whp.createDrawingPatriarch();
				insertImage(wb, patriarch,
						BitmapUtil.loadBitmapToByteArray(sigpath), i + 1, 17,
						1);
			}
			
			String imgurls[] = trafficinfo.getPic().split(Constans.PICPATHSPLITOR);
			for (int j = 0; j < imgurls.length; j++) {
				String path = imgurls[j];
				if (path.equalsIgnoreCase("null") || path.length() == 0) {
					continue;
				}
				HSSFPatriarch patriarch = sheet_whp.createDrawingPatriarch();
				insertImage(wb, patriarch,
						BitmapUtil.loadBitmapToByteArray(path), i + 1, 18 + j,
						1);
			}

			for (int k = 0; k < 18; k++) {
				Cell cell = row.getCell(k);
				cell.setCellStyle(cellStyle);
			}
		}
	}

	// 使用POI读入excel工作簿文件
	public static void readWorkBook() throws Exception {
		// poi读取excel
		// 创建要读入的文件的输入流
		InputStream inp = new FileInputStream("workbook.xls");

		// 根据上述创建的输入流 创建工作簿对象
		Workbook wb = WorkbookFactory.create(inp);
		// 得到第一页 sheet
		// 页Sheet是从0开始索引的
		Sheet sheet = wb.getSheetAt(0);
		// 利用foreach循环 遍历sheet中的所有行
		for (Row row : sheet) {
			// 遍历row中的所有方格
			for (Cell cell : row) {
				// 输出方格中的内容，以空格间隔
				System.out.print(cell.toString() + "  ");
			}
			// 每一个行输出之后换行
			System.out.println();
		}
		// 关闭输入流
		inp.close();
	}

	private static void insertImage(HSSFWorkbook wb, HSSFPatriarch pa,
			byte[] data, int row, int column, int index) {
		int x1 = 0;
		int y1 = 0;
		int x2 = x1 + 1023;
		int y2 = 255;
		HSSFClientAnchor anchor = new HSSFClientAnchor(x1, y1, x2, y2,
				(short) column, row, (short) column, row);
		anchor.setAnchorType(2);
		pa.createPicture(anchor,
				wb.addPicture(data, HSSFWorkbook.PICTURE_TYPE_JPEG));
	}
}
